# -*- encoding: utf-8 -*-
#import odoo.addons.decimal_precision as dp
from odoo import models, fields, api, _
from odoo.addons import base_cw
from odoo.exceptions import UserError
from odoo.tools.float_utils import float_round, float_compare



class statement_current_accounts_balance(models.Model):
    _name = 'statement.current.accounts.balance'
    _rec_name='sub_account_id'
    _description = u'来往余额表查询'

    period_id = fields.Many2one('account.period', u'期别')
    account_id = fields.Many2one('cncw.account', u'会计科目', ondelete="set null")
    dc_type = fields.Selection(base_cw.public.DC_TYPE, u'借贷', related='account_id.dc_type',readonly=True)
    sub_account_id = fields.Many2one('res.partner', u'辅助核算', ondelete="set null")

    begin = fields.Float(u'期初金额(原币)', digits='Amount')
    debit = fields.Float(u'本期借方(原币)', digits='Amount')
    credit = fields.Float(u'本期贷方(原币)', digits='Amount')
    balance = fields.Float(u'期末余额(原币)', digits='Amount')
    e1 = fields.Float(u'年初余额(原币)', digits='Amount')
    t1 = fields.Float(u'年累借(原币)', digits='Amount')
    u1 = fields.Float(u'年累贷(原币)', digits='Amount')

    lc_begin = fields.Float(u'期初金额', digits='Amount')
    lc_debit = fields.Float(u'本期借方', digits='Amount')
    lc_credit = fields.Float(u'本期贷方', digits='Amount')
    lc_balance = fields.Float(u'期末余额', digits='Amount')
    lc_e1 = fields.Float(u'年初余额', digits='Amount')
    lc_t1 = fields.Float(u'年累借', digits='Amount')
    lc_u1 = fields.Float(u'年累贷', digits='Amount')

    def init(self):
        self._cr.execute("""
                            CREATE OR REPLACE FUNCTION statement_current_accounts_balance_settle(
                                period_id_t integer,
                                pre_period_id_t integer,
                                master_id_t integer,
                                uid integer)
                              RETURNS void AS
                            $BODY$
                            -- 来往余额表查询
                            --DROP FUNCTION statement_current_accounts_balance_settle(integer,integer,integer,timestamp ,integer)
                            -- select statement_current_accounts_balance_settle(10,9,1,1)
                            -- select statement_current_accounts_balance_settle(11,10,1,1)
                            --select *  from account_period order by id
                            -- drop table account_balance
                            -- select *  from account_period
                            DECLARE
                                from_date timestamp;
                                to_date timestamp;
                                year_t character varying;   --年
                                rec record;
                            begin

                                select substring(name,4),date_start,date_stop into year_t,from_date,to_date from account_period where id=period_id_t;
                                create temp table account_balance_t(
                                  period_id integer, -- 期别
                                  master_id integer, -- 总帐月结处理
                                  account_id integer, -- 会计科目
                                  sub_account_id integer, -- 会科子目
                                  begin numeric, -- 期初金额(原币)
                                  debit numeric, -- 本期借方(原币)
                                  credit numeric, -- 本期贷方(原币)
                                  balance numeric, -- 期末余额(原币)

                                  e1 numeric,--年初余额
                                  t1 numeric,--年累借
                                  u1 numeric,--年累贷

                                  lc_begin numeric, -- 期初金额
                                  lc_debit numeric, -- 本期借方发生额
                                  lc_credit numeric, -- 本期贷方发生额
                                  lc_balance numeric, -- 期末借方金额

                                  lc_e1 numeric,--年初余额
                                  lc_t1 numeric,--年累借
                                  lc_u1 numeric--年累贷
                                );

                                create temp table account_balance_ok(
                                  period_id integer, -- 期别
                                  master_id integer, -- 总帐月结处理
                                  account_id integer, -- 会计科目
                                  sub_account_id integer, -- 会科子目
                                  begin numeric, -- 期初金额(原币)
                                  debit numeric, -- 本期借方(原币)
                                  credit numeric, -- 本期贷方(原币)
                                  balance numeric, -- 期末余额(原币)

                                  e1 numeric,--年初余额
                                  t1 numeric,--年累借
                                  u1 numeric,--年累贷

                                  lc_begin numeric, -- 期初金额
                                  lc_debit numeric, -- 本期借方发生额
                                  lc_credit numeric, -- 本期贷方发生额
                                  lc_balance numeric, -- 期末借方金额

                                  lc_e1 numeric,--年初余额
                                  lc_t1 numeric,--年累借
                                  lc_u1 numeric--年累贷
                                );

                                ----0.期初
                                    --取上期 期初金额 年初余额,
                                --
                                insert into account_balance_t(account_id,sub_account_id,begin,e1,t1,u1,lc_begin,lc_e1,lc_t1,lc_u1)
                                select a.account_id,a.sub_account_id,balance
                                       ,case when EXTRACT(MONTH from from_date)=1 then balance else e1 end as e1 --1月份为期末余额  否则为 年初余额
                                       ,case when EXTRACT(MONTH from from_date)=1 then 0 else t1 end as t1  --1月份为0  否则为 上期年累
                                       ,case when EXTRACT(MONTH from from_date)=1 then 0 else u1 end as u1
                                       ,lc_balance
                                       ,case when EXTRACT(MONTH from from_date)=1 then lc_balance else lc_e1 end as lc_e1  --1月份为期末余额  否则为 年初余额
                                       ,case when EXTRACT(MONTH from from_date)=1 then 0 else lc_t1 end as lc_t1
                                       ,case when EXTRACT(MONTH from from_date)=1 then 0 else lc_u1 end as lc_u1
                                  from statement_current_accounts_balance  a left join cncw_account e on a.account_id=e.id
                                                                     left join subaccount_category d on e.subaccount_category_id=d.id
                                 where e.sub_account_type='has'
                                   and d.code in ('customer','supplier','employee') --客户/供应商/员工
                                   and a.account_id is not null
                                   and a.sub_account_id is not null
                                   and period_id=pre_period_id_t;


                                --- 1.当期
                                insert into account_balance_t(account_id,sub_account_id,
                                                              debit,credit,lc_debit,lc_credit)
                                select a.account_id,c.sub_account_id,
                                       sum(coalesce(debit,0)) as debit,
                                       sum(coalesce(credit,0)) as credit,
                                   sum(coalesce(lc_debit,0)) as lc_debit,
                                   sum(coalesce(lc_credit,0)) as lc_credit
                                  from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                                      left join sub_account_line c on a.id=c.voucher_line_id
                                                      left join cncw_account e on a.account_id=e.id
                                                      left join subaccount_category d on e.subaccount_category_id=d.id
                                 where 1=1
                                   and b.date between from_date and to_date
                                   and b.state='audited'
                                   and e.sub_account_type='has'
                                   and d.code in ('customer','supplier','employee') --客户/供应商/员工
                                   and a.account_id is not null
                                   and c.sub_account_id is not null
                                group by a.account_id,c.sub_account_id;
                              --2.汇总
                              insert into account_balance_ok(account_id,sub_account_id,debit,credit,lc_debit,lc_credit,begin,e1,t1,u1,lc_begin,lc_e1,lc_t1,lc_u1)
                              select account_id,sub_account_id,
                                       sum(coalesce(debit,0)) as debit,
                                       sum(coalesce(credit,0)) as credit,
                                   sum(coalesce(lc_debit,0)) as lc_debit,
                                   sum(coalesce(lc_credit,0)) as lc_credit,
                                       sum(coalesce(begin,0)) as begin,
                                       sum(coalesce(e1,0)) as e1,
                                       sum(coalesce(t1,0)) as t1,
                                       sum(coalesce(u1,0)) as u1,
                                       sum(coalesce(lc_begin,0)) as lc_begin,
                                       sum(coalesce(lc_e1,0)) as lc_e1,
                                       sum(coalesce(lc_t1,0)) as lc_t1,
                                       sum(coalesce(lc_u1,0)) as lc_u1
                              from account_balance_t
                              group by account_id,sub_account_id;
                             --3.更新
                             update account_balance_ok a set t1=coalesce(debit,0)+coalesce(t1,0),
                                                           u1=coalesce(credit,0)+coalesce(u1,0),
                                                           balance=(coalesce(begin,0)+coalesce(debit,0)-coalesce(credit,0))*(case when b.dc_type='D' then 1 else -1 end),
                                                           lc_t1=coalesce(lc_debit,0)+coalesce(lc_t1,0),
                                                           lc_u1=coalesce(lc_credit,0)+coalesce(lc_u1,0),
                                                           lc_balance=(coalesce(lc_begin,0)+coalesce(lc_debit,0)-coalesce(lc_credit,0))*(case when b.dc_type='D' then 1 else -1 end)
                              from cncw_account b
                             where a.account_id=b.id;
                            --4.写入
                            delete from statement_current_accounts_balance where period_id=period_id_t;
                            insert into statement_current_accounts_balance(period_id,account_id,sub_account_id,begin,debit,credit,balance,e1,t1,u1,lc_begin,lc_debit,lc_credit,lc_balance,lc_e1,lc_t1,lc_u1)
                            select period_id_t,account_id,sub_account_id,begin,debit,credit,balance,e1,t1,u1,lc_begin,lc_debit,lc_credit,lc_balance,lc_e1,lc_t1,lc_u1
                              from account_balance_ok;
                              drop table account_balance_ok;
                              drop table account_balance_t;
                             end;
                             $BODY$
                              LANGUAGE plpgsql VOLATILE
                              COST 100;
                         """)